package droid.insane.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import droid.insane.logic.ConstantValues;

public class DBOpenHelper extends SQLiteOpenHelper {

	public static final String DATABASE_NAME = "lkfinance.db";
	private static final int DATABASE_VERSION = 1;

	// Database creation sql statements
	private static final String CREATE_TABLE_MARKETDATA = "CREATE TABLE "
			+ DbConstants.TABLE_MARKET_DATA + "( " + DbConstants.COLUMN_SYMBOL
			+ " TEXT PRIMARY KEY, " + DbConstants.COLUMN_COMPANY_NAME
			+ " TEXT NOT NULL, " + DbConstants.COLUMN_SHARE_VOLUME
			+ " TEXT NOT NULL, " + DbConstants.COLUMN_TRADE_VOLUME
			+ " TEXT NOT NULL, " + DbConstants.COLUMN_PREV_CLOSE
			+ " TEXT NOT NULL, " + DbConstants.COLUMN_OPEN + " TEXT NOT NULL, "
			+ DbConstants.COLUMN_HIGH + " TEXT NOT NULL, "
			+ DbConstants.COLUMN_LOW + " TEXT NOT NULL, "
			+ DbConstants.COLUMN_LAST_TRADED_PRICE + " FLOAT NOT NULL, "
			+ DbConstants.COLUMN_CHANGE_RS + " TEXT NOT NULL, "
			+ DbConstants.COLUMN_CHANGE_PRECENTAGE + " TEXT NOT NULL, "
			+ DbConstants.COLUMN_UP_OR_DOWN + " TEXT NOT NULL); ";

	private static final String CREATE_TABLE_PORTFOLIO_NAME = "CREATE TABLE "
			+ DbConstants.TABLE_PORTFOLIO_NAME + "( "
			+ DbConstants.COLUMN_PORTFOLIO_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ DbConstants.COLUMN_PORTFOLIO_NAME + " TEXT NOT NULL);";

	private static final String CREATE_TABLE_TRANSACTION = "CREATE TABLE `"
			+ DbConstants.TABLE_TRANSACTION + "` ( "
			+ DbConstants.COLUMN_TRANSACTION_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + DbConstants.COLUMN_STOCK
			+ " VARCHAR(10) NOT NULL, " + DbConstants.COLUMN_TYPE
			+ " TEXT NOT NULL, " + DbConstants.COLUMN_QUANTITY
			+ " INT NOT NULL, " + DbConstants.COLUMN_COST + " FLOAT NOT NULL);";

	private static final String CREATE_TABLE_PORTFOLIO = "CREATE TABLE "
			+ DbConstants.TABLE_PORTFOLIO + "( "
			+ DbConstants.COLUMN_PORTFOLIO_ID + " INT NOT NULL, "
			+ DbConstants.COLUMN_STOCK + " VARCHAR(10) NOT NULL, "
			+ DbConstants.COLUMN_QUANTITY + " INT NOT NULL, "
			+ DbConstants.COLUMN_COST + " FLOAT NOT NULL, " + "PRIMARY KEY ("
			+ DbConstants.COLUMN_PORTFOLIO_ID + "," + DbConstants.COLUMN_STOCK
			+ "), " + "FOREIGN KEY (" + DbConstants.COLUMN_PORTFOLIO_ID
			+ ") REFERENCES " + DbConstants.TABLE_PORTFOLIO_NAME + "("
			+ DbConstants.COLUMN_PORTFOLIO_ID + ") " + "ON DELETE CASCADE) ;";

	private static final String CREATE_TABLE_TRANSACTION_PORT = "CREATE TABLE "
			+ DbConstants.TABLE_TRANSACTION_PORTFOLIO + " ( "
			+ DbConstants.COLUMN_TRANSACTION_ID + " INT NOT NULL, "
			+ DbConstants.COLUMN_PORTFOLIO_ID + " INT NOT NULL, "
			+ "PRIMARY KEY (" + DbConstants.COLUMN_TRANSACTION_ID + ","
			+ DbConstants.COLUMN_PORTFOLIO_ID + ")," + " FOREIGN KEY ("
			+ DbConstants.COLUMN_TRANSACTION_ID + ") REFERENCES `"
			+ DbConstants.TABLE_TRANSACTION + "`("
			+ DbConstants.COLUMN_TRANSACTION_ID + ") " + "ON DELETE CASCADE, "
			+ "FOREIGN KEY (" + DbConstants.COLUMN_PORTFOLIO_ID
			+ ") REFERENCES " + DbConstants.TABLE_PORTFOLIO_NAME + " ("
			+ DbConstants.COLUMN_PORTFOLIO_ID + ") " + "ON DELETE CASCADE);";

	private static final String DEFAULT_PORTFOLIO = "INSERT INTO "
			+ DbConstants.TABLE_PORTFOLIO_NAME + " ("
			+ DbConstants.COLUMN_PORTFOLIO_NAME + ") VALUES ('Default');";

	public DBOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
	 * .SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase database) {

		Log.d(ConstantValues.TAG, "creating tables");
		try {
			database.execSQL(CREATE_TABLE_MARKETDATA);
			database.execSQL(CREATE_TABLE_PORTFOLIO_NAME);
			database.execSQL(CREATE_TABLE_TRANSACTION);
			database.execSQL(CREATE_TABLE_PORTFOLIO);
			database.execSQL(CREATE_TABLE_TRANSACTION_PORT);
			database.execSQL(DEFAULT_PORTFOLIO);
		} catch (Exception e) {
			Log.e(ConstantValues.TAG, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
	 * .SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(ConstantValues.TAG,
				"DBOpenHelper - Upgrading database from version " + oldVersion
						+ " to " + newVersion
						+ ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS" + DbConstants.TABLE_MARKET_DATA);
		db.execSQL("DROP TABLE IF EXISTS" + DbConstants.TABLE_PORTFOLIO_NAME);
		db.execSQL("DROP TABLE IF EXISTS" + DbConstants.TABLE_PORTFOLIO);
		db.execSQL("DROP TABLE IF EXISTS" + DbConstants.TABLE_TRANSACTION);
		db.execSQL("DROP TABLE IF EXISTS"
				+ DbConstants.TABLE_TRANSACTION_PORTFOLIO);

		onCreate(db);
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
		if (!db.isReadOnly()) {
			// Enable foreign key constraints
			db.execSQL("PRAGMA foreign_keys=ON;");
		}

	}

}
